﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Http;
using Jw.Core;
using Jw.Models;
using Jw.Web.Areas.Pacm.Common;
using System.Text;
using System.Diagnostics;
using System.Data;
using Jw.Utils;
using System.IO;
using Jw.Utils.NPOI.HSSF.UserModel;
using Jw.Utils.NPOI.SS.UserModel;
using Jw.Web.App_Start;
using System.Net.Http;
using Newtonsoft.Json;
using Jw.Utils.Helper;


namespace Jw.Web.Areas.Pacm.Controllers
{

    public class DayReportController : BaseController
    {
        #region << 控制器基本页面（方法）
        /// <summary>
        /// 日报页面 
        /// </summary>
        /// <returns>id为报表模板id</returns>
        public ActionResult Index(string id)
        {
            ViewBag.ReportID = id;
            ViewBag.ReportName = "";
            r_Report obj = new r_ReportService().GetModel(ParamQuery.Instance().AndWhere("Report_Id", id));
            if (obj != null)
            {
                ViewBag.ReportName = obj.Report_Name;
            }
            return View();
        }
        /// <summary>
        /// 添加一条日报表数据 返回是否成功
        /// </summary>
        /// <returns></returns>
        public int Add()
        {
            var UserName = FormsAuth.GetUserData().UserName;
            var reportID = Request["reportID"];
            var year = Request["year"];
            var month = Request["month"];
            var day = Request["day"];
            var service = new r_ReportInfoService();
            //根据报表编号 年 月 日 进行判断
            if (service.GetModel(ParamQuery.Instance().AndWhere("Info_Year", year).AndWhere("Info_Month", month).AndWhere("Info_Day", day).AndWhere("Info_ReportID", reportID)) == null)
            {
                int result = service.Insert(ParamInsert.Instance().Column("Info_ReportID", reportID).Column("Info_Year", year).Column("Info_Month", month).Column("Info_Day", day).Column("CreatePerson", UserName).Column("UpdatePerson", UserName).Column("CreateDate", DateTime.Now).Column("UpdateDate", DateTime.Now));
                if (result != 0)
                    return 1;//修改完成
                else
                    return 0;//修改失败
            }
            else
            {
                return 3;//记录已经存在了！
            }
        }
        /// <summary>
        /// 删除之前判断一下该表对应的数据表是否已经有数据了
        /// </summary>
        /// <param name="id"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public bool CheckDelete(string id, RequestWrapper query)
        {
            var service = new r_ZbsjService();
            r_Zbsj result = service.GetModel(ParamQuery.Instance().AndWhere("Zbsj_InfoID", id));
            if (result != null)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 根据ID删除某一条记录 返回受影响的行数
        /// </summary>
        /// <param name="id"></param>
        /// <param name="query"></param>
        /// <returns></returns>
        public int Delete(string id, string all, RequestWrapper query)
        {
            int result = 0;
            var service = new r_ReportInfoService();
            result = service.Delete(ParamDelete.Instance().AndWhere("Info_Id", id));
            if (all.ToLower() == "true")
            {
                var services = new r_ZbsjService();
                services.Delete(ParamDelete.Instance().AndWhere("Zbsj_InfoID", id));
            }
            return result;
        }
        /// <summary>
        /// 审核
        /// </summary>
        /// <param name="type">审核还是撤销(check,cancel)</param>
        /// <returns></returns>
        public int Check()
        {
            var UserName = FormsAuth.GetUserData().UserName;
            string id = Request["id"];
            string type = Request["type"];
            var service = new r_ReportInfoService();
            int result = 0;
            if (type == "check")
                result = service.Update(ParamUpdate.Instance().Column("ApproveState", "passed").Column("ApprovePerson", UserName).Column("ApproveDate", DateTime.Now).AndWhere("Info_Id", id));
            else
                result = service.Update(ParamUpdate.Instance().Column("ApproveState", "").Column("ApprovePerson", "").Column("ApproveDate", null).AndWhere("Info_Id", id));
            return result;
        }
        /// <summary>
        /// 编辑报表模板的页面
        /// </summary>
        /// <returns></returns>
        public ActionResult RirectEditReport(int id)
        {
            ViewBag.InfoID = id;
            ViewBag.H = ResolveWebFormat(id);
            return View();
        }
        /// <summary>
        /// 下载Excel 
        /// </summary>
        /// <returns></returns>
        public void downDayReport()
        {
            IWorkbook hssfworkbook = null;
            var infoId = Request["id"]; //info_id；
            var service = new r_ReportService();
            var infoService = new r_ReportInfoService();
            var valueService = new r_ZbsjService();
            r_ReportInfo ri = infoService.GetModel(ParamQuery.Instance().AndWhere("Info_Id", infoId)); //report_id;
            r_Report mExcel = service.GetModel(ParamQuery.Instance().AndWhere("Report_Id", ri.Info_ReportID));

            #region 重写Excel
            using (MemoryStream ms = new MemoryStream(mExcel.Report_ExportTemplete))
            {
                hssfworkbook = new HSSFWorkbook(ms);
                ISheet sheet = hssfworkbook.GetSheetAt(0);
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        ICell cell = row.Cells[j];
                        if (cell.StringCellValue.Contains("Calc"))
                        {
                            var valueModel = valueService.GetModel(ParamQuery.Instance().AndWhere("Zbsj_InfoID", infoId).AndWhere("Zbsj_Zbbh", cell.StringCellValue.Replace("[Calc]", "")));
                            if (valueModel != null)
                            {
                                cell.SetCellValue(valueModel.Zbsj_Value);
                            }
                            else
                            {
                                cell.SetCellValue("");
                            }
                        }
                    }
                }
            }
            #endregion
            string fileName = Path.GetTempFileName(); //临时文件
            // string fileName = string.Format("Re_{0}{1}", FormsAuth.GetUserData().UserCode + infoId, ".xls");
            // string mapPath = Server.MapPath("").Substring(0, Server.MapPath("").IndexOf("pacm")) + "\\Areas\\pacm" + "\\download\\" + fileName;
            if (System.IO.File.Exists(fileName)) System.IO.File.Delete(fileName);
            using (FileStream file = new FileStream(fileName, FileMode.Create))
            {
                hssfworkbook.Write(file);
                Jw.Utils.ZFiles.DownloadFile(System.Web.HttpContext.Current, file, string.Format("{0}.xls", mExcel.Report_Name), 1024102410);

            };

        }


        public string GetNewCalcs(string formula, string reportId)
        {
            //获取四则运算顺序
            formula = formula.Replace(" ", "");

            string[] split = formula.Split(new Char[] { '+', '-', '*', '/' });
            string sReturn = string.Empty;
            for (int i = 0; i < split.Length; i++)
            {
                string stemp = split[i].Replace("[Calc]", "").Replace("(", "").Replace(")", "");
                r_Zbzd obj = new r_ZbzdService().GetModel(new ParamQuery().AndWhere("Zbzd_ReportID", reportId).AndWhere("Zbzd_Zbbh", stemp));
                if (obj != null)
                {
                    if (string.IsNullOrWhiteSpace(obj.Zbzd_Formula))
                    {
                        if (i == (split.Length - 1))
                            sReturn += split[i];
                        else
                            sReturn += split[i] + formula.Substring(formula.IndexOf(split[i]) + split[i].Length, 1);
                    }
                    else
                    {
                        obj.Zbzd_Formula = "(" + obj.Zbzd_Formula + ")";
                        if (i == (split.Length - 1))
                        {
                            if (split[i].Contains(")"))
                                sReturn += GetNewCalcs(obj.Zbzd_Formula, reportId) + ")";
                            else
                                sReturn +=  GetNewCalcs(obj.Zbzd_Formula, reportId) ;
                        }
                        else
                        {
                            if (split[i].Contains(")"))
                                sReturn += GetNewCalcs(obj.Zbzd_Formula, reportId) + formula.Substring(formula.IndexOf(split[i]) + split[i].Length, 1) + ")";
                            else
                                sReturn +=  GetNewCalcs(obj.Zbzd_Formula, reportId) + formula.Substring(formula.IndexOf(split[i]) + split[i].Length, 1) ;
                        }
                    }
                }
                else
                {
                    //不是指标
                    if (i == (split.Length - 1))
                        sReturn += split[i];
                    else
                        sReturn += split[i] + formula.Substring(formula.IndexOf(split[i]) + split[i].Length, 1);
                }
            }
            //要考虑带括号
            return sReturn;
        }

        /// <summary>
        /// 保存指标（计算公式并保存）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult formulaCount()
        {
            try
            {
                var infoId = Request["id"]; //日报id
                var infoService = new r_ReportInfoService();
                r_ReportInfo ri = infoService.GetModel(ParamQuery.Instance().AndWhere("Info_Id", infoId));
                if (ri == null) return Json(new { success = "error", message = "没有报表相关信息" }); ; //获取日报表编码获取完成
                var ReportId = ri.Info_ReportID;//根据日报id获取日报编码
                var service = new r_ZbzdService(); //查询出该报表公式类型为calc的列表
                // List<r_Zbzd> lists = new List<r_Zbzd>();
                //    service.GetModelList(ParamQuery.Instance().AndWhere("Zbzd_ReportID", ReportId).AndWhere("Zdzd_Gslx", "Calc"));

                //                var lists = service.GetDynamicList(ParamQuery.Instance().Select(string.Format(@"select r_Zbzd.* from r_ReportContents 
                //                                                            left outer join  r_Zbzd on  Zbzd_ReportID = Contents_ReportId and Contents_Text = '[Calc]' + Zbzd_Zbbh
                //                                                            where Zbzd_ReportID = '{0}'", ReportId)));
                string sql = string.Format(@"select r_Zbzd.* from r_ReportContents 
                                            left outer join  r_Zbzd on  Zbzd_ReportID = Contents_ReportId and Contents_Text = '[Calc]' + Zbzd_Zbbh
                                            where Zbzd_ReportID = '{0}' and Zdzd_Gslx = 'Calc'", ReportId);

                using (var db = Db.Context("Pacm"))
                {
                    var lists = db.Sql(sql).QueryMany<dynamic>();



                    var valueService = new r_ZbsjService();
                    int result = 0;
                    for (int i = 0; i < lists.Count; i++)
                    {
                        
                        string newFormula = GetNewCalcs(lists[i].Zbzd_Formula, ReportId);

                        System.Text.RegularExpressions.MatchCollection sR = System.Text.RegularExpressions.Regex.Matches(newFormula, @"(\[Calc\]\d{6})", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                        foreach (System.Text.RegularExpressions.Match m in sR)
                        {
                            r_Zbsj sj = valueService.GetModel(ParamQuery.Instance().AndWhere("Zbsj_InfoID", infoId).AndWhere("Zbsj_Zbbh", m.Value.Replace("[Calc]", ""))); //获取指定报表下的指定calc编码的数值
                            if (sj != null)
                            {
                                if (sj.Zbsj_Value == "") sj.Zbsj_Value = "0";
                                newFormula = newFormula.Replace(m.Value, sj.Zbsj_Value);
                            }
                            else
                                newFormula = newFormula.Replace(m.Value, "0");

                        }
                        float resultvalue = CalcStringExpression.CalcByJs(newFormula); //new Jw.Utils.Helper.Calculator().Calculate(sTemp);//

                        r_Zbsj haveRb = valueService.GetModel(ParamQuery.Instance().AndWhere("Zbsj_InfoID", infoId).AndWhere("Zbsj_Zbbh", lists[i].Zbzd_Zbbh));

                        if (haveRb == null) //证明数据库还没有存在
                        {
                            int re = valueService.Insert(ParamInsert.Instance().Column("Zbsj_InfoID", infoId).Column("Zbsj_Zbbh", lists[i].Zbzd_Zbbh).Column("Zbsj_Value", resultvalue));
                            if (re != 0)
                                result++;
                        }
                        else //存在的时候进行更新
                        {
                            int re = valueService.Update(ParamUpdate.Instance().Column("Zbsj_Value", resultvalue).AndWhere("Zbsj_InfoID", infoId).AndWhere("Zbsj_Zbbh", lists[i].Zbzd_Zbbh));
                            if (re != 0)
                                result++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return Json(new { success = "error", message = ex.Message });
            }
            return Json(new { success = "ok", message = "计算成功" });
        }
        #endregion

        /// <summary>
        /// 根据报表信息得到报表模板格式
        /// </summary>
        /// <param name="infoId"></param>
        /// <returns></returns>
        private string ResolveWebFormat(int infoId = 3)
        {
            string[] splitText = new string[] { "", "&nbsp;", "&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" };
            Stopwatch sw = new Stopwatch();
            sw.Start();//用来计算报表加载时间，开始
            var reportInfoService = new r_ReportInfoService();
            r_ReportInfo reportInfo = reportInfoService.GetModel(ParamQuery.Instance().AndWhere("Info_Id", infoId));
            List<r_ReportContents> dataList = new r_ReportContentsService().GetModelList(ParamQuery.Instance().AndWhere("Contents_ReportId", reportInfo.Info_ReportID));
            List<r_Zbzd> ZbzdList = new r_ZbzdService().GetModelList(ParamQuery.Instance().AndWhere("Zbzd_ReportID", reportInfo.Info_ReportID));
            List<r_Zbsj> zbsjList = new r_ZbsjService().GetModelList(ParamQuery.Instance().AndWhere("Zbsj_InfoID", infoId));
            r_Report report = new r_ReportService().GetModel(ParamQuery.Instance().AndWhere("Report_Id", reportInfo.Info_ReportID));
            if (report.Report_ColWidth == null) return "";
            string[] arrcolwidths = report.Report_ColWidth.Split(',');//取得报表每列的宽度
            int collength = arrcolwidths.Length;//取得报表列数
            string[] arrrowheights = report.Report_RowHeight.Split(',');//取得报表每行的高度
            bool isCurrOrgId = true;
            StringBuilder Builder = new StringBuilder();
            Builder.AppendFormat("<input type='hidden' id='Info_ReportID' name='Info_ReportID' value='{0}' />\n", reportInfo.Info_ReportID);
            Builder.AppendFormat("<input type='hidden' id='Info_DwId' name='Info_DwId' value='{0}' />\n", reportInfo.Info_Bmbh);
            Builder.AppendFormat("<input type='hidden' id='Info_Year' name='Info_Year' value='{0}' />\n", reportInfo.Info_Year);
            Builder.AppendFormat("<input type='hidden' id='Info_Month' name='Info_Month' value='{0}' />\n", reportInfo.Info_Month);
            Builder.AppendFormat("<input type='hidden' id='Info_Day' name='Info_Day' value='{0}' />\n", reportInfo.Info_Day);
            Builder.AppendFormat("<input type='hidden' id='Report_Cycle' name='Report_Cycle' value='{0}'/>\n", report.Report_Cycle);
            Builder.AppendFormat("<input type='hidden' id='Info_Id' name='SubmitInfo_ID' value='{0}'/>\n", reportInfo.Info_Id);
            //如果不是报表所在用户打开报表，显示为只读状态
            Builder.AppendFormat("<input type='hidden' id='Info_Status' name='Info_Status' value='{0}'/>\n", isCurrOrgId ? reportInfo.Info_Status : "1");
            Builder.Append("</form>\n");
            //Builder.Append("<table id='TableMain' style='position:absolute;width:{0}px;left:0px;table-layout:fixed;z-index:100;display:none;'>\n");
            Builder.Append("<table id='TableMain'>\n");
            Builder.Append("<colgroup>\n");
            int allwidth = 0;
            for (int m = 0; m < collength; m++)//设置报表每列宽度
            {
                Builder.AppendFormat("<col style='width:{0}px'>\n", arrcolwidths[m]);
                allwidth = allwidth + int.Parse(arrcolwidths[m]);
            }
            Builder.Append("</colgroup>\n");
            Builder = new StringBuilder(string.Format(Builder.ToString(), allwidth));

            int curRow = 0;
            int j = 0;
            int tRow = 0, tCol = 0, RowSpan = 0, ColSpan = 0, Inden = 0;
            string Text = string.Empty;
            string ZB = string.Empty;
            string cssText = string.Empty;
            string ZBValue = string.Empty;
            string Formula = string.Empty;
            foreach (r_ReportContents Contents in dataList)//dataList中每一行数据，对应表格中的一个单元格，设置每个单元格样式
            {
                ZB = string.Empty;
                ZBValue = string.Empty;
                Formula = string.Empty;
                tRow = Contents.Contents_Row;// int.Parse(dr["CONTENTS_ROW"].ToString());
                tCol = Contents.Contents_Col;//int.Parse(dr["CONTENTS_COL"].ToString());
                RowSpan = Contents.Contents_RowSpan.Value;
                ColSpan = Contents.Contents_ColSpan.Value;

                //Formula = string.IsNullOrEmpty(Contents.Contents_Format) ? "" : "Formula='" + Contents.CONTENTS_Formula + "'";//指标公式
                //ZB = (Contents.Contents_IsEdit == "1" || Formula != "") ? Contents.Contents_Text : "";//如果该单元格允许编辑或者是计算公式，则返回指标编号
                ZBValue = Contents.Contents_Text;
                if (Contents.Contents_Text.Contains("[Calc]"))
                {
                    ZBValue = "";
                    ZB = Contents.Contents_Text.Replace("[Calc]", "");//指标编号
                    r_Zbzd objZbzd = ZbzdList.Find(delegate(r_Zbzd p) { return p.Zbzd_Zbbh == ZB; });
                    if (objZbzd != null)
                        Formula = objZbzd.Zbzd_Formula;
                    r_Zbsj zbsj = zbsjList.Find(delegate(r_Zbsj o) { return o.Zbsj_Zbbh == ZB; }); //根据报表编号、年、月、日、指标编号查的指标值
                    if (zbsj != null)
                        ZBValue = zbsj.Zbsj_Value;

                }
                Text = ZBValue;
                Inden = Convert.ToInt32(Contents.Contents_Indentation);//缩进
                cssText = "";
                cssText = "font-family:" + Contents.Contents_FontName + ";font-size:" + Contents.Contents_FontSize + "pt;" + (Contents.Contents_FontBold == null ? "" : Contents.Contents_FontBold + ";") + "overflow: hidden;" + Contents.Contents_H + ";" + Contents.Contents_V + ";" + Contents.Contents_BorderStyle;
                //有公式就设置个背景色
                if (!string.IsNullOrEmpty(Formula))
                {
                    cssText += ";background-color:#DCDCDC";
                }
                //数据类型，如果是数字附加到td的属性上
                string datatypeatt = string.Empty;
                if (!string.IsNullOrEmpty(Contents.Contents_Format))
                {
                    if (Contents.Contents_Format.StartsWith("1"))
                    {
                        //附加小数位数，同时有这个属性表示是数值类型
                        datatypeatt = " digits='" + Contents.Contents_Format.Split(',')[1] + "'";
                    }
                }
                if (tRow > 0 && curRow > 0 && curRow != tRow)
                {
                    Builder.Append("</tr>\n");
                }
                if (curRow != tRow)
                {
                    Builder.AppendFormat("<tr style='height:{4}px'>\n  <td  rowspan={0} colspan={1} style='{3}' row={5} col={6} isEdit={7} ZB='{9}'>{8}{2}</td>\n", RowSpan, ColSpan, Text, cssText, arrrowheights[j], Contents.Contents_Row, Contents.Contents_Col, Contents.Contents_IsEdit, splitText[Inden], ZB);
                    j++;
                }
                else
                {
                    Builder.AppendFormat(" <td rowspan={0} colspan={1}  style='{3}' row={4} col={5} isEdit={6} ZB='{8}'{9}>{7}{2}</td>\n", RowSpan, ColSpan, Text, cssText, Contents.Contents_Row, Contents.Contents_Col, isCurrOrgId ? Contents.Contents_IsEdit : "0", splitText[Inden], ZB, datatypeatt);
                }
                curRow = tRow;
            }
            Builder.Append("</tr>\n</table>\n");
            sw.Stop();//用来计算报表加载时间，结束
            Builder.Append("<span style='font-size:12px;'>总运行时间：" + sw.ElapsedMilliseconds + "毫秒</span>\n");
            return Builder.ToString();
        }
        public ActionResult SaveZbsj(int InfoId, string Zbbh, string Zbsj)
        {
            //判断是否存在 如果不存在则插入
            r_ZbsjService service = new r_ZbsjService();
            int iReturn = 0;
            r_Zbsj objZbsj = service.GetModel(ParamQuery.Instance().AndWhere("Zbsj_InfoID", InfoId).AndWhere("Zbsj_Zbbh", Zbbh));
            if (objZbsj == null)
            {
                iReturn = service.Insert(ParamInsert.Instance().Column("Zbsj_InfoID", InfoId).Column("Zbsj_Zbbh", Zbbh).Column("Zbsj_Value", Zbsj));
            }
            else
            {
                iReturn = service.Update(ParamUpdate.Instance().Column("Zbsj_Value", Zbsj).AndWhere("Zbsj_InfoID", InfoId).AndWhere("Zbsj_Zbbh", Zbbh));
            }
            return Json(new { success = "ok" });
        }


        /// <summary>
        /// 下载之前进行判断
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult CheckDownReport(int id)
        {
            r_ReportInfo obj = new r_ReportInfoService().GetModel(new ParamQuery().AndWhere("Info_Id", id));
            if (obj == null)
            {
                return Json(new { success = "error", message = "报表信息不存在，数据错误!" });
            }
            //
            r_Report m = new r_ReportService().GetModel(new ParamQuery().AndWhere("Report_Id", obj.Info_ReportID));
            if (m.Report_ExportTemplete == null)
            {
                return Json(new { success = "error", message = "模板不存在，无法下载!" });
            }
            else
            {
                return Json(new { success = "ok", message = "" });
            }


        }
    }


    public class DayReportApiController : ApiController
    {
        public dynamic Get(string id, RequestWrapper query)
        {
            query.LoadSettingXmlString(@"
            <settings defaultOrderBy='Info_Id'>
                <select>
                    A.*,B.Report_Name,A.Info_Year +'-' + A.Info_Month  +'-' +  A.Info_Day as Cxrq 
                </select>
                <from>
                    r_ReportInfo A
                    left join r_Report       B on B.Report_Id      = A.Info_ReportID
                </from>
                <where defaultForAll='true' defaultCp='equal' defaultIgnoreEmpty='true' >
                    <field name='Info_Id'             cp='equal'      ></field>
                </where>
            </settings>
            ");
            var pQuery = query.ToParamQuery().AndWhere("A.Info_ReportID", id);
            var list = new r_ReportInfoService().GetDynamicListWithPaging(pQuery);
            return list;
        }

    }

}
